﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Diagnostics;
using LOGISTCBUS.Entities;
using LOGISTCBUS.Entities.Enums;

namespace LOGISTCBUS.DAO
{
    public class StudentDAO : BaseDAO
    {
        #region public methods
        public Boolean insert(Student s)
        {
            var db = new Db();
            db.Connect();
            String sql = "insert into students (name, cpf, rg, birthdate, address, id_city, phone, cellular, sex, description) ";
            sql += "values (@name, @cpf, @rg, @birthdate, @address, @id_city, @phone, @cellular, @sex, @description);";
            var parameters = new Dictionary<String, Object>();
            parameters["@name"] = s.Name;
            parameters["@cpf"] = s.CPF;
            parameters["@rg"] = s.RG;
            parameters["@birthdate"] = s.BirthDate;
            parameters["@address"] = s.Address;
            parameters["@id_city"] = s.City != null && s.City.Id > 0 ? s.City.Id : new Nullable<Int32>();
            parameters["@phone"] = s.Phone;
            parameters["@cellular"] = s.Cellular;
            parameters["@sex"] = s.Sex != null && Convert.ToInt32(s.Sex) > 0 ? Convert.ToInt32(s.Sex) : new Nullable<Int32>();
            parameters["@description"] = s.Description;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Boolean update(Student s)
        {
            var db = new Db();
            db.Connect();
            String sql = "update students set name = @name, cpf = @cpf, rg = @rg, birthdate = @birthdate, address = @address, id_city = @id_city, ";
            sql += "phone = @phone, cellular = @cellular, sex = @sex, description = @description where id= @id;";
            var parameters = new Dictionary<String, Object>();
            parameters["@name"] = s.Name;
            parameters["@cpf"] = s.CPF;
            parameters["@rg"] = s.RG;
            parameters["@birthdate"] = s.BirthDate;
            parameters["@address"] = s.Address;
            parameters["@id_city"] = s.City != null && s.City.Id > 0 ? s.City.Id : new Nullable<Int32>();
            parameters["@phone"] = s.Phone;
            parameters["@cellular"] = s.Cellular;
            parameters["@sex"] = s.Sex != null && Convert.ToInt32(s.Sex) > 0 ? Convert.ToInt32(s.Sex) : new Nullable<Int32>();
            parameters["@description"] = s.Description;
            parameters["@id"] = s.Id;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Student selectById(Int32 id)
        {
            var db = new Db();
            db.Connect();
            var dt = db.Select("select * from students where id = " + id);
            db.Disconnect();
            if (dt == null || dt.Rows.Count <= 0)
                return null;
            DataRow r = dt.Rows[0];
            var obj = new Student()
            {
                Id = Convert.ToInt32(r["id"]),
                Name = r["name"].ToString(),
                CPF = r["cpf"] is DBNull ? string.Empty : r["cpf"].ToString(),
                RG = r["rg"] is DBNull ? string.Empty : r["rg"].ToString(),
                BirthDate = r["birthdate"] is DBNull ? new Nullable<DateTime>() : Convert.ToDateTime(r["birthdate"]),
                Address = r["address"] is DBNull ? string.Empty : r["address"].ToString(),
                City = r["id_city"] is DBNull ? new City() : new City() { Id = Convert.ToInt32(r["id_city"]) },
                Phone = r["phone"] is DBNull ? string.Empty : r["phone"].ToString(),
                Cellular = r["cellular"] is DBNull ? string.Empty : r["cellular"].ToString(),
                Sex = r["sex"] is DBNull ? new Nullable<ESex>() : (ESex)Convert.ToInt32(r["sex"]),
                Description = r["description"] is DBNull ? string.Empty : r["description"].ToString(),
                Removed = Convert.ToBoolean(r["removed"])
            };
            return obj;
        }

        public List<Student> select()
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Student>();
            var dt = db.Select("select * from students where removed = 0 order by name");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Student()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Name = r["name"].ToString(),
                    CPF = r["cpf"] is DBNull ? string.Empty : r["cpf"].ToString(),
                    RG = r["rg"] is DBNull ? string.Empty : r["rg"].ToString(),
                    BirthDate = r["birthdate"] is DBNull ? new Nullable<DateTime>() : Convert.ToDateTime(r["birthdate"]),
                    Address = r["address"] is DBNull ? string.Empty : r["address"].ToString(),
                    City = r["id_city"] is DBNull ? new City() : new City() { Id = Convert.ToInt32(r["id_city"]) },
                    Phone = r["phone"] is DBNull ? string.Empty : r["phone"].ToString(),
                    Cellular = r["cellular"] is DBNull ? string.Empty : r["cellular"].ToString(),
                    Sex = r["sex"] is DBNull ? new Nullable<ESex>() : (ESex)Convert.ToInt32(r["sex"]),
                    Description = r["description"] is DBNull ? string.Empty : r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public List<Student> select(String name, Int32 page, Int32 pageSize)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Student>();
            var sql = "select * from students where removed = 0";
            if (!String.IsNullOrEmpty(name))
                sql += " and name like '%" + name + "%'";
            sql += " order by name";
            var dt = db.Select(sql);
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Student()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Name = r["name"].ToString(),
                    CPF = r["cpf"] is DBNull ? string.Empty : r["cpf"].ToString(),
                    RG = r["rg"] is DBNull ? string.Empty : r["rg"].ToString(),
                    BirthDate = r["birthdate"] is DBNull ? new Nullable<DateTime>() : Convert.ToDateTime(r["birthdate"]),
                    Address = r["address"] is DBNull ? string.Empty : r["address"].ToString(),
                    City = r["id_city"] is DBNull ? new City() : new City() { Id = Convert.ToInt32(r["id_city"]) },
                    Phone = r["phone"] is DBNull ? string.Empty : r["phone"].ToString(),
                    Cellular = r["cellular"] is DBNull ? string.Empty : r["cellular"].ToString(),
                    Sex = r["sex"] is DBNull ? new Nullable<ESex>() : (ESex)Convert.ToInt32(r["sex"]),
                    Description = r["description"] is DBNull ? string.Empty : r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst.Skip((page - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<Student> selectStudents(Int32 idTrip, Boolean hasNot)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Student>();
            var sql = "select * from students s where" + (hasNot ? " s.removed = 0 and " : " ") + "s.id" + (hasNot ? " not " : " ") + "in (select st.id_student from student_trips st where st.removed = 0 and st.id_trip = " + idTrip + ") order by s.name";
            var dt = db.Select(sql);
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Student()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Name = r["name"].ToString(),
                    CPF = r["cpf"] is DBNull ? string.Empty : r["cpf"].ToString(),
                    RG = r["rg"] is DBNull ? string.Empty : r["rg"].ToString(),
                    BirthDate = r["birthdate"] is DBNull ? new Nullable<DateTime>() : Convert.ToDateTime(r["birthdate"]),
                    Address = r["address"] is DBNull ? string.Empty : r["address"].ToString(),
                    City = r["id_city"] is DBNull ? new City() : new City() { Id = Convert.ToInt32(r["id_city"]) },
                    Phone = r["phone"] is DBNull ? string.Empty : r["phone"].ToString(),
                    Cellular = r["cellular"] is DBNull ? string.Empty : r["cellular"].ToString(),
                    Sex = r["sex"] is DBNull ? new Nullable<ESex>() : (ESex)Convert.ToInt32(r["sex"]),
                    Description = r["description"] is DBNull ? string.Empty : r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public Int32 getCount(String name)
        {
            var count = 0;
            var db = new Db();
            db.Connect();
            var lst = new List<Student>();
            var sql = "select count(*) as qtd from students where removed = 0";
            if (!String.IsNullOrEmpty(name))
                sql += " and name like '%" + name + "%'";
            var dt = db.Select(sql);
            db.Disconnect();
            if (dt != null && dt.Rows.Count > 0)
            {
                var r = dt.Rows[0];
                count = Convert.ToInt32(r["qtd"]);
            }
            return count;
        }

        public List<Student> select(String filter)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Student>();
            var dt = db.Select("select * from students where removed = 0 and " + getConditions(filter, "name") + " order by name");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Student()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Name = r["name"].ToString(),
                    CPF = r["cpf"] is DBNull ? string.Empty : r["cpf"].ToString(),
                    RG = r["rg"] is DBNull ? string.Empty : r["rg"].ToString(),
                    BirthDate = r["birthdate"] is DBNull ? new Nullable<DateTime>() : Convert.ToDateTime(r["birthdate"]),
                    Address = r["address"] is DBNull ? string.Empty : r["address"].ToString(),
                    City = r["id_city"] is DBNull ? new City() : new City() { Id = Convert.ToInt32(r["id_city"]) },
                    Phone = r["phone"] is DBNull ? string.Empty : r["phone"].ToString(),
                    Cellular = r["cellular"] is DBNull ? string.Empty : r["cellular"].ToString(),
                    Sex = r["sex"] is DBNull ? new Nullable<ESex>() : (ESex)Convert.ToInt32(r["sex"]),
                    Description = r["description"] is DBNull ? string.Empty : r["description"].ToString(),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public Boolean delete(Student s)
        {
            var db = new Db();
            db.Connect();
            String sql = getSQLDeleteUpdate("students", "name");
            var parameters = new Dictionary<String, Object>();
            parameters["@id"] = s.Id;
            var sucess = db.Execute(sql, parameters);
            db.Commit();
            db.Disconnect();
            return sucess;
        }

        public Boolean delete(List<Student> lst)
        {
            var db = new Db();
            db.Connect();
            var sucess = true;
            foreach (var s in lst)
            {
                String sql = getSQLDeleteUpdate("students", "name");
                var parameters = new Dictionary<String, Object>();
                parameters["@id"] = s.Id;
                sucess = db.Execute(sql, parameters);
            }
            db.Commit();
            db.Disconnect();
            return sucess;
        }
        #endregion
    }
}
